const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'Lycc.2025',
    database: 'fun_english_db',
    charset: 'utf8mb4'
};

// 年级映射
const gradeMap = {
    '三上': '三上',
    '三下': '三下',
    '四上': '四上',
    '四下': '四下',
    '五上': '五上',
    '五下': '五下',
    '六上': '六上',
    '六下': '六下'
};

// 解析Markdown文件（改进版）
function parseVocabularyFile(filePath) {
    const content = fs.readFileSync(filePath, 'utf-8');
    const lines = content.split('\n');
    const vocabulary = [];
    const skippedLines = [];
    
    for (let lineNum = 0; lineNum < lines.length; lineNum++) {
        const line = lines[lineNum].trim();
        
        // 跳过空行和标题行
        if (!line || line.startsWith('>') || line.startsWith('#')) {
            continue;
        }
        
        // 匹配格式：序号 课本 主题 单词 音标 中文
        // 改进的正则表达式，更灵活
        const match = line.match(/^(\d+)\s+(三上|三下|四上|四下|五上|五下|六上|六下)\s+(Unit\s+\d+)\s+([^\s]+)\s+\/([^\/]+)\/\s+(.+?)$/);
        
        if (match) {
            const [, id, grade, unit, word, phonetic, chinese] = match;
            vocabulary.push({
                id: parseInt(id),
                grade: gradeMap[grade],
                unit: unit.trim(),
                word: word.trim(),
                phonetic: `/${phonetic.trim()}/`,
                chinese: chinese.trim()
            });
        } else {
            // 尝试更宽松的匹配（处理特殊格式）
            const altMatch = line.match(/^(\d+)\s+(三上|三下|四上|四下|五上|五下|六上|六下)\s+(Unit\s+\d+)\s+([^\s\/]+)\s*\/([^\/]+)\/\s*(.*)$/);
            if (altMatch) {
                const [, id, grade, unit, word, phonetic, chinese] = altMatch;
                vocabulary.push({
                    id: parseInt(id),
                    grade: gradeMap[grade],
                    unit: unit.trim(),
                    word: word.trim(),
                    phonetic: `/${phonetic.trim()}/`,
                    chinese: chinese.trim() || word.trim() // 如果中文为空，用单词本身
                });
            } else if (/^\d+\s+(三上|三下|四上|四下|五上|五下|六上|六下)/.test(line)) {
                // 记录无法解析但看起来像单词行的内容
                skippedLines.push({ lineNum: lineNum + 1, content: line });
            }
        }
    }
    
    return { vocabulary, skippedLines };
}

// 导入数据到数据库
async function importVocabulary() {
    let connection;
    
    try {
        console.log('=================================');
        console.log('趣味英语 - 单词数据导入（修复版）');
        console.log('=================================\n');
        
        console.log('连接数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功\n');
        
        console.log('读取词汇文件...');
        const vocabFilePath = path.join(__dirname, '../docs/tomarkdown.org-2025-10-09T07-31-18.md');
        const { vocabulary, skippedLines } = parseVocabularyFile(vocabFilePath);
        
        console.log(`✅ 成功解析 ${vocabulary.length} 个单词\n`);
        
        if (skippedLines.length > 0) {
            console.log(`⚠️  跳过了 ${skippedLines.length} 行无法解析的内容:`);
            skippedLines.slice(0, 5).forEach(item => {
                console.log(`   行 ${item.lineNum}: ${item.content.substring(0, 60)}...`);
            });
            if (skippedLines.length > 5) {
                console.log(`   ... 还有 ${skippedLines.length - 5} 行\n`);
            }
        }
        
        // 清空现有数据
        console.log('清空现有词汇数据...');
        await connection.execute('DELETE FROM vocabulary');
        console.log('✅ 清空完成\n');
        
        // 批量插入
        console.log('开始导入数据...');
        const insertQuery = `
            INSERT INTO vocabulary (word, phonetic, chinese, grade, unit)
            VALUES (?, ?, ?, ?, ?)
        `;
        
        let successCount = 0;
        let errorCount = 0;
        const errors = [];
        
        for (let item of vocabulary) {
            try {
                await connection.execute(insertQuery, [
                    item.word,
                    item.phonetic,
                    item.chinese,
                    item.grade,
                    item.unit
                ]);
                successCount++;
                
                if (successCount % 100 === 0) {
                    console.log(`  已导入 ${successCount} 个单词...`);
                }
            } catch (error) {
                errorCount++;
                errors.push({ word: item.word, error: error.message });
            }
        }
        
        console.log('\n=================================');
        console.log('导入完成统计');
        console.log('=================================');
        console.log(`✅ 成功导入: ${successCount} 个单词`);
        if (errorCount > 0) {
            console.log(`❌ 导入失败: ${errorCount} 个单词`);
            errors.slice(0, 5).forEach(e => {
                console.log(`   - ${e.word}: ${e.error}`);
            });
        }
        
        // 显示统计信息
        const [stats] = await connection.execute(`
            SELECT grade, COUNT(*) as count 
            FROM vocabulary 
            GROUP BY grade 
            ORDER BY FIELD(grade, '三上', '三下', '四上', '四下', '五上', '五下', '六上', '六下')
        `);
        
        console.log('\n=================================');
        console.log('各年级单词数量');
        console.log('=================================');
        let total = 0;
        stats.forEach(row => {
            console.log(`  ${row.grade}: ${row.count} 个单词`);
            total += row.count;
        });
        console.log(`  ---------------------------------`);
        console.log(`  总计: ${total} 个单词`);
        
        // 检查是否达到848个
        if (total === 848) {
            console.log('\n✅ 完美！成功导入全部848个单词！');
        } else {
            console.log(`\n⚠️  注意: 预期848个单词，实际导入${total}个，差异${848 - total}个`);
        }
        
        console.log('=================================\n');
        
    } catch (error) {
        console.error('❌ 导入过程出错:', error.message);
        console.error(error.stack);
    } finally {
        if (connection) {
            await connection.end();
            console.log('数据库连接已关闭\n');
        }
    }
}

// 执行导入
if (require.main === module) {
    importVocabulary().catch(console.error);
}

module.exports = { parseVocabularyFile, importVocabulary };

